/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package mvc.model;

/**
 *
 * @author Alex
 */
import java.io.*;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;


public class Model implements Serializable{

    protected static Connection con;
    private String jdbcDriver;
    private String jdbcUrl;
    private String user;
    private String password;
    private String database;

  


    public String getJdbcDriver() {
        return jdbcDriver;
    }

    public void setJdbcDriver(String jdbcDriver) {
        this.jdbcDriver = jdbcDriver;
    }

    public String getJdbcUrl() {
        return jdbcUrl;
    }

    public void setJdbcUrl(String jdbcUrl) {
        this.jdbcUrl = jdbcUrl;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getDatabase() {
        return database;
    }

    public void setDatabase(String database) {
        this.database = database;
    }
    
    public void conectar() throws SQLException {
        try {
            Class.forName(this.jdbcDriver);
        } catch (ClassNotFoundException ex) {
            throw new SQLException("Driver no cargado !!!"+ex.toString());
        }
        System.out.println("------------Conexion Driver--------- :"+this.getJdbcUrl()+this.getDatabase()+","+this.getUser()+","+this.getPassword());
        con = DriverManager.getConnection(this.getJdbcUrl()+this.getDatabase(), this.getUser(), this.getPassword());
    }
    public boolean isConnect()
    {
        if(con!=null)
        {
           return true;
        }else
        {
           return false;
        }
    }
    public void disconnect()
    {
        if(con!=null)
        {
            try
            {
              con.close();
            }catch(SQLException ignore){}
            finally
            {
              con=null;
            }
        }
    }




  
  public void agregarUsuario(Usuario per)throws SQLException{
                if(existeUsuario(per))
                    throw new SQLException("Ya existe el Usuario");
		PreparedStatement pstmt=null;

		try{

			pstmt=con.prepareStatement("INSERT INTO USUARIO VALUES (?,?,?,?,?,?,?,?)");

			pstmt.setInt(1,per.getId());
			pstmt.setString(2,per.getNombre());
			pstmt.setString(3,per.getApellidoUno());
			pstmt.setString(4,per.getApellidoDos());
                        pstmt.setInt(5,per.getTelefono());
			pstmt.setString(6,per.getDireccion());
			pstmt.setString(7,per.getEmail());
			pstmt.setString(8,per.getContraseña());
                       
			pstmt.executeUpdate();
		}finally{
			if(pstmt!=null){
				pstmt.close();
				pstmt=null;
			}
		}

  }
  public boolean existeUsuario(Usuario per) throws SQLException{

		boolean sw=true;
		PreparedStatement pstmt=null;
		ResultSet rs=null;

		try{
			pstmt=con.prepareStatement("SELECT COUNT(*) FROM USUARIO WHERE id=?");
			pstmt.setInt(1, per.getId());
			rs=pstmt.executeQuery();
			if(rs.next())
				if(rs.getString(1).equals("0"))
					sw=false;
		}finally{
			if(rs!=null)
				rs.close();

			if(pstmt!=null)
				pstmt.close();
		}

		return sw;
	}

  public void agregarProducto(Producto pro)throws SQLException{
                if(existeProducto(pro))
                    throw new SQLException("Ya existe el Producto");
		PreparedStatement pstmt=null;

		try{

			pstmt=con.prepareStatement("INSERT INTO PRODUCTOS VALUES (?,?,?,?)");

			
                        
                        pstmt.setString(1,pro.getCodigo());
                        pstmt.setString(2,pro.getNombre());
                        pstmt.setDouble(3,pro.getValor());
			pstmt.setString(4,pro.getDescripcion());


			pstmt.executeUpdate();
		}finally{
			if(pstmt!=null){
				pstmt.close();
				pstmt=null;
			}
		}

  }
  public boolean existeProducto(Producto pro) throws SQLException{

		boolean sw=true;
		PreparedStatement pstmt=null;
		ResultSet rs=null;

		try{
			pstmt=con.prepareStatement("SELECT COUNT(*) FROM PRODUCTOS WHERE codigo=?");
			pstmt.setString(1, pro.getCodigo());
			rs=pstmt.executeQuery();
			if(rs.next())
				if(rs.getString(1).equals("0"))
					sw=false;
		}finally{
			if(rs!=null)
				rs.close();

			if(pstmt!=null)
				pstmt.close();
		}

		return sw;
	}

  public Producto ExtraeProducto (String cod) throws SQLException{
                if (!isConnect())
			throw new SQLException("no hay conexcion");
                Producto p= new Producto();
		PreparedStatement pstmt=null;
		ResultSet rs=null;

		try{
			pstmt=con.prepareStatement("SELECT * FROM PRODUCTOS WHERE codigo=?");
			pstmt.setString(1, cod);
			rs=pstmt.executeQuery();
			if(rs.next()){
			  p=this.loadProducto(rs);

                        }else{

                        }
		}finally{
			if(rs!=null)
				rs.close();

			if(pstmt!=null)
				pstmt.close();
		}

		return p;
     }
  public List listarProductos() throws SQLException {
        if (!isConnect())
		throw new SQLException("no hay conexcion");

        PreparedStatement ps = null;
        ResultSet rs = null;
        Producto c = null;
        List lista = new LinkedList();
        try {
            ps =(PreparedStatement)  con.prepareStatement("select * from productos ");
            rs = ps.executeQuery();

            while (rs.next()) {
                c = this.loadProducto(rs);
                lista.add(c);
            }

        } finally {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
        return lista;
    }

  private Producto loadProducto(ResultSet rs) throws SQLException {
        Producto p = new Producto();

        p.setCodigo(rs.getString(1));
        p.setNombre(rs.getString(2));
        p.setValor(rs.getDouble(3));
        p.setDescripcion(rs.getString(4));

        return p;
    }

  public void agregarProveedor(Proveedor prov)throws SQLException{
                if(existeProveedor(prov))
                    throw new SQLException("Ya existe el Provedor");
		PreparedStatement pstmt=null;

		try{

			pstmt=con.prepareStatement("INSERT INTO PROVEEDOR VALUES (?,?,?,?,?,?)");

			pstmt.setString(1, prov.getCodigo());
                        pstmt.setString(2, prov.getNombre());
			pstmt.setString(3, prov.getApellidoUno());
			pstmt.setInt(4, prov.getTelefono());
			pstmt.setString(5, prov.getDireccion());
			pstmt.setString(6, prov.getEmail());
			

			pstmt.executeUpdate();
		}finally{
			if(pstmt!=null){
				pstmt.close();
				pstmt=null;
			}
		}

  }
  public boolean existeProveedor(Proveedor prov) throws SQLException{

		boolean sw=true;
		PreparedStatement pstmt=null;
		ResultSet rs=null;

		try{
			pstmt=con.prepareStatement("SELECT COUNT(*) FROM PROVEEDOR WHERE codigo=?");
			pstmt.setString(1, prov.getCodigo());
			rs=pstmt.executeQuery();
			if(rs.next())
				if(rs.getString(1).equals("0"))
					sw=false;
		}finally{
			if(rs!=null)
				rs.close();

			if(pstmt!=null)
				pstmt.close();
		}

		return sw;
	}

}
